package com.alpha.dao;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.TreeMap;
import java.util.Date;

import javax.naming.NamingException;


import com.alpha.bom.Answer;
import com.alpha.bom.Exam;
import com.alpha.bom.ExamResponse;
import com.alpha.bom.Question;
import com.alpha.bom.Student;
import com.alpha.dao.interfaces.IExamResponseDao;

public class ExamResponseDao extends AlphaDao implements IExamResponseDao{
	
	public TreeMap<Question, Answer> retrieveExamResponseAnswers(int examResponseId) throws NamingException, SQLException {
		TreeMap<Question, Answer> answers = new TreeMap<Question, Answer>();
		this.connect();
		String select = "SELECT * FROM tbl_student_response WHERE exam_student_id="+examResponseId;
		try {
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int questionId = this.resultSet.getInt("question_id");
				QuestionDao qDao = new QuestionDao();
				Question question = qDao.retrieveQuestion(questionId);
				
				int answerId = this.resultSet.getInt("answer_id");
				AnswerDao aDao = new AnswerDao();
				Answer answer = aDao.retrieveAnswer(answerId);
				answers.put(question, answer);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return answers;
	}
	
	public ExamResponse retrieveExamResponse(int examResponseId) throws NamingException, SQLException {
		ExamResponse response = null;
		this.connect();
		String select = "SELECT * FROM tbl_exam_student WHERE exam_student_id="+examResponseId;
		try {
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int studentId = this.resultSet.getInt("student_id");
				StudentDao sDao = new StudentDao();
				Student student = sDao.retrieveStudent(studentId);
				
				int examId = this.resultSet.getInt("exam_id");
				ExamDao eDao = new ExamDao();
				Exam exam = eDao.retrieveExamById(examId);
				
				response = new ExamResponse(exam, student);
				response.setExamResponseId(examResponseId);
				response.setDateTaken(this.resultSet.getDate("date_taken"));
				if(response.getDateTaken() != null) {
					response.setComplete(true);
				}
				
				ExamResponseDao erDao = new ExamResponseDao();
				if(response.isComplete()) {
					response.setAnswers(erDao.retrieveExamResponseAnswers(examResponseId));
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return response;
	}

	public ArrayList<ExamResponse> retrieveExamResponseByStudent(int studentId) throws NamingException, SQLException {
		ArrayList<ExamResponse> responses = new ArrayList<ExamResponse>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam_student WHERE student_id="+studentId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int examResponseId = this.resultSet.getInt("exam_student_id");
				ExamResponseDao erDao = new ExamResponseDao();
				ExamResponse response = erDao.retrieveExamResponse(examResponseId);
				responses.add(response);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return responses;
	}
	
	public ArrayList<ExamResponse> retrieveExamResponseByExam(int examId) throws NamingException, SQLException {
		ArrayList<ExamResponse> responses = new ArrayList<ExamResponse>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam_student WHERE exam_id="+examId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int examResponseId = this.resultSet.getInt("exam_student_id");
				ExamResponseDao erDao = new ExamResponseDao();
				ExamResponse response = erDao.retrieveExamResponse(examResponseId);
				responses.add(response);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return responses;
	}
	
	public String retrieveAllCompleteStudents(int examId) throws NamingException, SQLException {
		String result = "";
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam_student WHERE exam_id="+examId+" AND date_taken is not NULL";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int examStudentId = this.resultSet.getInt("exam_student_id");
				String dateCompleted = this.resultSet.getDate("date_taken").toString();
				if(!result.equals("")) {
					result += ",";
				}
				result += "" + examStudentId + ":" + dateCompleted;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return result;
	}
	
	public int getNumberOfTakers(int examId) throws NamingException, SQLException {
		int number = 0;
		this.connect();
		try {
			String select = "SELECT COUNT(exam_student_id) FROM tbl_exam_student WHERE exam_id="+examId+" AND date_taken is NULL";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				number = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return number;
	}
	
	public int getNumberOfAllTakers(int examId) throws NamingException, SQLException {
		int number = 0;
		this.connect();
		try {
			String select = "SELECT COUNT(exam_student_id) FROM tbl_exam_student WHERE exam_id="+examId+" AND date_taken is not NULL";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				number = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			this.disconnect();
		}
		
		return number;
	}
	
	public boolean insertExamResponse(int studentId, int examId, int answerId, int questionId) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_student_response(exam_student_id, answer_id, question_id) VALUES (?,?,?)";
		try {
			ExamDao eDao = new ExamDao();
			int exam_student_id = eDao.retrieveLatestExamStudentId(examId, studentId);
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, exam_student_id);
			this.preparedStatement.setInt(2, answerId);
			this.preparedStatement.setInt(3, questionId);
			
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateExamResponse(int studentId, int examId, int answerId, int questionId) throws NamingException, SQLException {
		boolean success = false;
		ExamDao eDao = new ExamDao();
		int examStudentId = eDao.retrieveLatestExamStudentId(examId, studentId);
		
		this.connect();
		String insert = "UPDATE tbl_student_response INNER JOIN tbl_exam_student ON tbl_student_response.exam_student_id=tbl_exam_student.exam_student_id SET answer_id=? WHERE student_id="+studentId+" AND tbl_exam_student.exam_student_id="+examStudentId+" AND question_id="+questionId;
		try {
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, answerId);
			
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateDateTaken(int examId, int studentId, Date dateTaken) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String update = "UPDATE tbl_exam_student SET date_taken=? WHERE exam_id=? AND student_id=? AND date_taken is NULL";
		try {
			this.preparedStatement = this.connection.prepareStatement(update);
			this.preparedStatement.setTimestamp(1, new Timestamp(dateTaken.getTime()));
			this.preparedStatement.setInt(2, examId);
			this.preparedStatement.setInt(3, studentId);
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateDateTakenOfUntakenExamResponse(int examId) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String update = "UPDATE tbl_exam_student SET date_taken=? WHERE exam_id=? AND date_taken is NULL";
		try {
			this.preparedStatement = this.connection.prepareStatement(update);
			this.preparedStatement.setTimestamp(1, new Timestamp(new Date().getTime()));
			this.preparedStatement.setInt(2, examId);
			
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
}
